﻿'========================================================================
'功能:申請種類資料庫存取物件
'作者:andy
'日期: 2011/3/1
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data


Namespace Telecom.DAO
    Public Class TelTypeDAO
        Inherits OAMS.DAO.BaseDAO
        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub
        ''' <summary>
        ''' 取得某一廠的申請類別
        ''' </summary>
        ''' <param name="suit_site"></param>
        ''' <returns>取得某一廠的申請類別</returns>
        ''' <remarks>取得某一廠的申請類別</remarks>

        Public Function GetTelType(ByVal suit_site As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@suit_site", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = suit_site


            Dim sb As New StringBuilder()

            sb.AppendLine(" select 0 as canModi,convert(varchar(10),CrtDate,111) as CrtDate,convert(varchar(10),ModiDate,111) as ModiDate, A.* ,B.emp_name as CrtName,C.emp_name as ModiName from TEL_TYPE_C  A left join  AUOREF.dbo.emp_data_all  B  on A.CrtUID=B.emp_no collate Chinese_Taiwan_Stroke_CI_AS left join  AUOREF.dbo.emp_data_all C on A.ModiUID=C.emp_no  collate Chinese_Taiwan_Stroke_CI_AS where suit_site=@suit_site")

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function


        Public Function GetTelTypeInsert(ByVal suit_site As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@suit_site", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = suit_site


            Dim sb As New StringBuilder()
            sb.AppendLine("  select '' as Message,0 as FamilyCode, 0 as OnsiteFill, 1  as canModi ,'' as type_code,'' as type_name,'' as  suit_site ,'' as  use_type,'' as allow_modify_short_code,'' as allow_modify_phone_no,'' as allow_quit,'' as type_desc,0  as  NeedApprove,'' as CrtUID,null as  CrtDate,'' as ModiUID,null as  ModiDate,'' as CrtName,'' as ModiName ,  0 as NeedExt")
            sb.AppendLine(" union select Message , FamilyCode,OnsiteFill,0 as canModi ,type_code,type_name,suit_site ,use_type,allow_modify_short_code,allow_modify_phone_no,allow_quit,type_desc,NeedApprove,CrtUID,convert(varchar(10),CrtDate,111) as CrtDate,ModiUID,convert(varchar(10),ModiDate,111) as ModiDate ,B.emp_name as CrtName,C.emp_name as ModiName ,NeedExt from TEL_TYPE_C  A left join  AUOREF.dbo.emp_data_all  B  on A.CrtUID=B.emp_no  collate Chinese_Taiwan_Stroke_CI_AS  left join AUOREF.dbo.emp_data_all  C on A.ModiUID=C.emp_no  collate Chinese_Taiwan_Stroke_CI_AS  where A.suit_site=@suit_site  order by canModi desc ")


            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function

        ''' <summary>
        ''' 新增申請種類資料
        ''' </summary>
        ''' <param name="suit_site"></param>
        ''' <param name="UID"></param>
        ''' <remarks>新增申請種類資料</remarks>
        Public Sub InsertTelType(ByVal type_code As String, ByVal type_name As String, ByVal suit_site As String, ByVal type_desc As String, ByVal NeedApprove As Integer, ByVal NeedExt As Boolean, ByVal UID As String, ByVal OnsiteFill As Boolean, ByVal FamilyCode As Boolean, ByVal Message As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(10) {}

            sqlParams(0) = New SqlParameter("@type_name", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = type_name
            sqlParams(1) = New SqlParameter("@type_desc", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = type_desc
            sqlParams(2) = New SqlParameter("@NeedApprove", SqlDbType.Int)
            sqlParams(2).Value = NeedApprove
            sqlParams(3) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(3).Value = UID
            sqlParams(4) = New SqlParameter("@type_code", SqlDbType.NVarChar, 100)
            sqlParams(4).Value = type_code
            sqlParams(5) = New SqlParameter("@suit_site", SqlDbType.NVarChar, 100)
            sqlParams(5).Value = suit_site
            sqlParams(6) = New SqlParameter("@NeedExt", SqlDbType.Bit)
            sqlParams(6).Value = NeedExt
            sqlParams(7) = New SqlParameter("@OnsiteFill", SqlDbType.Bit)
            sqlParams(7).Value = OnsiteFill
            sqlParams(8) = New SqlParameter("@FamilyCode", SqlDbType.Bit)
            sqlParams(8).Value = FamilyCode
            sqlParams(9) = New SqlParameter("@Message", SqlDbType.NVarChar, Message.Length)
            sqlParams(9).Value = Message
            Dim sb As New StringBuilder()
            sb.AppendLine(" insert into  TEL_TYPE_C(type_code,type_name,suit_site ,use_type,allow_modify_short_code,allow_modify_phone_no,allow_quit,type_desc,NeedApprove,CrtUID,CrtDate,ModiUID,ModiDate,NeedExt,OnsiteFill,FamilyCode,Message) ")
            sb.AppendLine("values(@type_code,@type_name,@suit_site ,'','','','',@type_desc,@NeedApprove,@UID,getDate(),'',null,@NeedExt,@OnsiteFill,@FamilyCode,@Message)")

            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub
        ''' <summary>
        ''' 修改申請種類資料
        ''' </summary>
        ''' <param name="type_code"></param>
        ''' <param name="type_name"></param>
        ''' <param name="suit_site"></param>
        ''' <param name="type_desc"></param>
        ''' <param name="NeedApprove"></param>
        ''' <param name="UID"></param>
        ''' <remarks>修改申請種類資料</remarks>
        Public Sub UpdateTelType(ByVal type_code As String, ByVal type_name As String, ByVal suit_site As String, ByVal type_desc As String, ByVal NeedApprove As Integer, ByVal NeedExt As Boolean, ByVal UID As String, ByVal OnsiteFill As Boolean, ByVal FamilyCode As Boolean, ByVal Message As String)

            Dim sqlParams As SqlParameter() = New SqlParameter(10) {}

            sqlParams(0) = New SqlParameter("@type_name", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = type_name
            sqlParams(1) = New SqlParameter("@type_desc", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = type_desc
            sqlParams(2) = New SqlParameter("@NeedApprove", SqlDbType.Int)
            sqlParams(2).Value = NeedApprove
            sqlParams(3) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(3).Value = UID
            sqlParams(4) = New SqlParameter("@type_code", SqlDbType.NVarChar, 100)
            sqlParams(4).Value = type_code
            sqlParams(5) = New SqlParameter("@suit_site", SqlDbType.NVarChar, 100)
            sqlParams(5).Value = suit_site
            sqlParams(6) = New SqlParameter("@NeedExt", SqlDbType.Bit)
            sqlParams(6).Value = NeedExt
            sqlParams(7) = New SqlParameter("@OnsiteFill", SqlDbType.Bit)
            sqlParams(7).Value = OnsiteFill
            sqlParams(8) = New SqlParameter("@FamilyCode", SqlDbType.Bit)
            sqlParams(8).Value = FamilyCode
            sqlParams(9) = New SqlParameter("@Message", SqlDbType.NVarChar, Message.Length)
            sqlParams(9).Value = Message
            Dim sb As New StringBuilder()

            sb.AppendLine(" update  TEL_TYPE_C set type_name=@type_name,type_desc=@type_desc,NeedApprove=@NeedApprove,ModiUID=@UID ,ModiDate=getdate() ,NeedExt=@NeedExt , OnsiteFill=@OnsiteFill,FamilyCode=@FamilyCode,Message=@Message where  type_code=@type_code  and suit_site=@suit_site")


            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub


        ''' <summary>
        ''' 檢查申請類別是否存在
        ''' </summary>
        ''' <param name="suit_site"></param>
        ''' <param name="type_code"></param>
        ''' <returns>檢查申請類別是否存在</returns>
        ''' <remarks>檢查申請類別是否存在</remarks>
        Public Function type_codeIsExist(ByVal suit_site As String, ByVal type_code As String) As Boolean
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}

            sqlParams(0) = New SqlParameter("@suit_site", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = suit_site
            sqlParams(1) = New SqlParameter("@type_code", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = type_code
            Dim sb As New StringBuilder()
            sb.AppendLine(" select * from TEL_TYPE_C where   type_code=@type_code  and suit_site=@suit_site")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows.Count > 0
        End Function

        ''' <summary>
        ''' 取得某一筆的申請項目資料
        ''' </summary>
        ''' <param name="suit_site">廠別</param>
        ''' <param name="type_code">申請項目代號</param>
        ''' <returns>取得某一筆的申請項目資料</returns>
        ''' <remarks>取得某一筆的申請項目資料</remarks>
        Public Function GetTypeCode(ByVal suit_site As String, ByVal type_code As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}

            sqlParams(0) = New SqlParameter("@suit_site", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = suit_site
            sqlParams(1) = New SqlParameter("@type_code", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = type_code
            Dim sb As New StringBuilder()
            sb.AppendLine(" select * from TEL_TYPE_C where   type_code=@type_code  and suit_site=@suit_site")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function

    End Class


End Namespace


